﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using CRMFramework;
using Newtonsoft.Json;

namespace CRMBiz
{
    public static class EmailTemplateHandler
    {

        /// <summary>
        /// function to get column name
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>

        public static DataTable getColumnName()
        {
            DataTable dt;
            try
            {
                string sql = "SELECT * FROM view_getcolumnname";
                dt = DBhelper.ExecuteDataset(sql).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        /// <summary>
        /// function to Update email template
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>

        public static int UpdateEmailTemplate(int EmailId, string TemplateFormat, string emailSubject, int CategoryID)
        {
            int intReturnValue = 0;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@EmailId", EmailId),
                    DBhelper.SetParameter("@TemplateFormat", TemplateFormat),
                    DBhelper.SetParameter("@emailsubject",emailSubject),
                    DBhelper.SetParameter("@CategoryId", CategoryID),
                };

                DataSet ds = new DataSet();
                string sbSql = "Select * From crm_emailtemplate Where EmailId <> @EmailId And TemplateFormat = @TemplateFormat and  CategoryId= @CategoryId";
                ds = DBhelper.ExecuteDataset(sbSql, para);

                if (ds.Tables[0].Rows.Count > 0)
                {
                    intReturnValue = 0;
                }
                else
                {
                    sbSql = "update crm_emailtemplate set TemplateFormat = @TemplateFormat,emailsubject=@emailsubject, CategoryId= @CategoryId where EmailId = @EmailId";
                    DBhelper.ExecuteNonQuery(sbSql, para);
                    intReturnValue = 1;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intReturnValue;
        }

        /// <summary>
        /// function to Insert email template
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>

        public static int AddEmailTemplate(string EmailTemplate, string TemplateFormat, string emailSubject, int CategoryID)
        {
            int intReturnValue = 0;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@EmailTemplate", EmailTemplate),
                    DBhelper.SetParameter("@TemplateFormat", TemplateFormat),
                    DBhelper.SetParameter("@emailsubject",emailSubject),
                     DBhelper.SetParameter("@CategoryId", CategoryID),
                };

                DataSet ds = new DataSet();
                string sbSql = "Select * From crm_emailtemplate Where EmailTemplate = @EmailTemplate And TemplateFormat = @TemplateFormat AND emailsubject=@emailsubject and CategoryId= @CategoryId ";
                ds = DBhelper.ExecuteDataset(sbSql, para);

                if (ds.Tables[0].Rows.Count > 0)
                {
                    intReturnValue = 0;
                }
                else
                {
                    sbSql = "insert into crm_emailtemplate(EmailTemplate,TemplateFormat,emailsubject,CategoryId) values(@EmailTemplate,@TemplateFormat,@emailsubject,@CategoryId)";
                    DBhelper.ExecuteNonQuery(sbSql, para);
                    intReturnValue = 1;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intReturnValue;
        }

        /// <summary>
        /// function to bind email template
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>

        public static DataTable BindEmailTemplate()
        {
            DataTable dt;
            try
            {
                string sql = "SELECT EmailId,EmailTemplate FROM crm_emailtemplate";
                dt = DBhelper.ExecuteDataset(sql).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        /// <summary>
        /// function to Delete email template
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>

        public static bool DeleteEmailTemplate(int EmailId)
        {
            bool flag = false;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@EmailId", EmailId),
                };

                string sbSql = "delete from crm_emailtemplate where EmailId=@EmailId";
                DBhelper.ExecuteNonQuery(sbSql, para);
                flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        /// <summary>
        /// function to bind emailId's
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static DataTable BindEmailId()
        {
            DataTable dt;
            try
            {
                string sql = "SELECT * FROM crm_emailaccount WHERE IsDefault=1";
                dt = DBhelper.ExecuteDataset(sql).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        /// <summary>
        /// function to select password for default
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static DataTable SelectPassword()
        {
            DataTable dt;
            try
            {
                string sql = "SELECT Password as setPwd,EmailId FROM `crm_emailaccount` WHERE IsDefault=1";
                dt = DBhelper.ExecuteDataset(sql).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        /// <summary>
        /// function to bind otherApplicants
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>

        public static string BindAppandContact(int app_id, int contact_id)
        {
            string str = "";
            StringBuilder sql = new StringBuilder();
            DataTable dt = new DataTable();
            if (app_id == 0 && contact_id != 0)
            {
                sql = new StringBuilder("SELECT CONCAT(crm_contact.FirstName,' ',crm_contact.SurName) AS FullName,crm_contact.Email FROM crm_contact WHERE crm_contact.ContactID='" + contact_id + "'");
            }
            else
            {
                sql = new StringBuilder("SELECT CONCAT(crm_contact.FirstName,' ',crm_contact.SurName) AS FullName,crm_contact.Email FROM crm_applicationcontact INNER JOIN crm_contact ON crm_applicationcontact.ContactID = crm_contact.ContactID WHERE crm_applicationcontact.app_id ='" + app_id + "'");
            }
            dt = DBhelper.ExecuteDataset(sql.ToString()).Tables[0];
            if (dt.Rows.Count > 0)
            {
                ArrayList oList = new ArrayList();
                string strJson = JsonConvert.SerializeObject(dt);
                str += "[";
                for (int i = 0; i <= dt.Rows.Count - 1; i++)
                {
                    str += "{";
                    for (int j = 0; j <= dt.Columns.Count - 1; j++)
                    {
                        oList.Add(dt.Columns[j].ColumnName + ":" + dt.Rows[i][j].ToString());
                        if (dt.Columns.Count == j + 1)
                        {
                            str += Convert.ToString("\"" + dt.Columns[j].ColumnName + "\"" + ":" + "\"" + dt.Rows[i][j].ToString() + "\"");
                        }
                        else
                        {
                            str += Convert.ToString("\"" + dt.Columns[j].ColumnName + "\"" + ":" + "\"" + dt.Rows[i][j].ToString() + "\"" + ",");
                        }
                    }
                    if (dt.Rows.Count == i + 1)
                    {
                        str += "}";
                    }
                    else
                    {
                        str += "},";
                    }
                }
                str += "]";
            }
            return str;
        }
    }
}
